package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.dao.DefinePaymentTypeDAOImpls.TRAN_MODE;
import com.mbc.rmt.data.CashPaymentDataList;
import com.mbc.rmt.entities.CashPayment;
import com.mbc.rmt.entities.CashPaymentCri;
import com.mbc.rmt.entities.CashPaymentDetails;
import com.mbc.rmt.entities.CashPaymentRpt;

public class CashPaymentDAOImpls extends DAO<CashPayment> implements CashPaymentDAO{
	
	public final String field_slip_id = "slipid";
	public final String field_slip_no = "slipno";
	public final String field_created_date = "createddate";
	public final String field_modified_date = "modifieddate";
	public final String field_tr_date = "trdate";
	public final String field_created_by = "createdby";
	public final String field_modified_by = "modifiedby";
	public final String field_agent_id = "agentid";
	public final String field_frombranch_id = "frombranchid";
	public final String field_defaultbranch_id = "defaultbranchid";
	public final String field_currency_id = "currencyid";
	public final String field_paymentamount = "paymentamount";
	public final String field_bank_id = "bankid";
	public final String field_bank_code = "bankcode";
	public final String field_tr_type = "trtype";
	public final String field_remark = "remark";
	
	public final String field_agent_code = "agent_code";
	public final String field_brance_code = "branchcode";
	
	enum R_MODE { HEADER, DETAIL }
	
	public CashPaymentDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "cashpayment";
	}

	@Override
	public void create(CashPayment entity) throws SQLException {
		query = insertParam(table_name, new String[] { field_slip_id, field_slip_no,
				field_created_date ,field_modified_date ,field_tr_date, field_created_by,
				field_modified_by, field_agent_id,field_defaultbranch_id,
				field_currency_id,field_paymentamount,field_bank_id,field_tr_type,field_remark });
		
		pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.SAVE);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(CashPayment entity) throws SQLException {
		query = updateParam(table_name, new String[] { field_slip_id, field_slip_no,
				field_modified_date ,field_tr_date, field_modified_by, field_agent_id ,field_defaultbranch_id,field_currency_id,
				field_paymentamount,field_bank_id,field_tr_type,field_remark}) + " AND " + field_slip_id + " = " + entity.getSlipID();		
		pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.UPDATE);
		pstmt.executeUpdate();
		pstmt.close();
		
	}
	
	@Override
	public void delete(CashPayment entity) throws SQLException {
		query = "DELETE FROM "+table_name+" WHERE "+field_slip_id+" = "+entity.getSlipID();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();		
	}

	@Override
	public CashPayment find(long key) throws SQLException {
		CashPayment cashpayment = null;
		query = getSelectQuery() + " where slipid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			cashpayment = readRecord(rs);
		rs.close();
		pstmt.close();
		return cashpayment;
	}	
	
	@Override
	public CashPaymentDataList findCashPayment(CashPaymentCri cri)
			throws SQLException {
		CashPaymentDataList l_DataList = new CashPaymentDataList();
		ArrayList<CashPayment> l_entityList = new ArrayList<CashPayment>();
		//String l_cri = " and cp.slipno like N'%" + cri.getKeyword() + "%'" +
					   //" and cp.trtype = "+cri.getTrtype()+
		String l_cri = "";
		
		if(cri.getFieldname().equals("TRDate"))
			l_cri += " and (Convert(varchar(100),cp." + cri.getFieldname() + ", 103)) like N'%" + cri.getKeyword() + "%'";
		else
			l_cri += " and cp." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
					   
		//l_cri += " and cp.trtype = "+ cri.getTrtype() + " and cp.defaultbranchid = "+cri.getBranchId();
		
		if(cri.getTrtype() == 5)
			l_cri += " and a.agent_id = " + cri.getAgentid();
		
		if(cri.getTrtype() > 4) {
			int type = cri.getTrtype() - 4;
			l_cri += " and cp.trtype = " + type + " and cpd.ReturnStatus = 0 and cp.defaultbranchid = "+cri.getBranchId();
		} else {
			if(cri.getTrtype() == 0)
				l_cri += " and cp.defaultbranchid = "+cri.getBranchId();
			else
				l_cri += " and cp.trtype = " + cri.getTrtype() + " and cp.defaultbranchid = "+cri.getBranchId();
		}
		
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by cp.slipno desc) as [rowno],cp.*, ";
		
		if(cri.getTrtype() > 4) {
			query += "cpd.SlipDetailID, cpd.InvoiceNo, cpd.ReceiptNo, cpd.Sender, cpd.Receiver, cpd.[Address], cpd.frombranchid, " +
					"cpd.CurrencyID as D_CurrencyID,c.CurrencyCode, cpd.Rate, cpd.ApplyAmount, cpd.DefineDetailID, cpd.type_code, ";
		}
			
		query += " a.agent_code,bk.BankCode";//+
				//" FROM cashpayment cp " +
		
		if(cri.getTrtype() > 4)
			query += " FROM CashPaymentDetails cpd " +
					" INNER JOIN cashpayment cp ON cp.SlipID = cpd.SlipID " +
					" INNER JOIN Currency c on c.CurrencyId = cpd.CurrencyID ";
		else
			query += " FROM cashpayment cp ";
		
		query += " LEFT JOIN agent a ON a.agent_id = cp.agentid"+
				" left join Bank bk on bk.BankId = cp.BankID" +
				" where 1 = 1" + l_cri + ")a"
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				if(cri.getTrtype() > 4)
					l_entityList.add(readRecords(rs, R_MODE.DETAIL));
				else
					l_entityList.add(readRecords(rs, R_MODE.HEADER));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		
		if(cri.getTrtype() > 4)
			query = "select count(SlipDetailID) from CashPaymentDetails cpd " +
					"inner join cashpayment cp on cp.SlipID = cpd.SlipID " +
					"LEFT JOIN agent a ON a.agent_id = cp.agentid Where 1=1 " + l_cri;
		else
			query = "select count(slipno) from " +table_name +" cp Where 1=1 "
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	
	@Override
	public CashPayment getCashPayment(CashPayment entity) throws SQLException {
		// TODO Auto-generated method stub
		CashPayment cashpayment = null;
		query = "SELECT cp.*," +
				"ISNULL(a.agent_code,'')agent_code,bk.BankCode " +
				"FROM cashpayment cp " +
				" LEFT JOIN agent a ON a.agent_id = cp.agentid"+
				" left join Bank bk on bk.BankId = cp.BankID" +
				" Where cp.slipno = '"+entity.getSlipno()+"'" +
				" And cp.defaultbranchid = "+entity.getDefaultbranchID()+
				" And cp.trtype = "+entity.getTrtype();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			cashpayment = readRecords(rs, R_MODE.HEADER);
		rs.close();
		pstmt.close();
		return cashpayment;
	}
	
	@Override
	protected CashPayment readRecord(ResultSet rs) throws SQLException {
		CashPayment entity = new CashPayment();
		entity.setSlipID(rs.getLong(field_slip_id));
		entity.setSlipno(rs.getString(field_slip_no));
		entity.setCreatedDate(rs.getDate(field_created_date));
		entity.setModifiedDate(rs.getDate(field_modified_date));
		entity.setTrdate(rs.getDate(field_tr_date));
		entity.setCreatedBy(rs.getLong(field_created_by));
		entity.setModifiedBy(rs.getLong(field_modified_by));
		entity.setAgentID(rs.getLong(field_agent_id));
		entity.setDefaultbranchID(rs.getLong(field_defaultbranch_id));
		entity.setCurrencyID(rs.getLong(field_currency_id));
		entity.setPaymentamt(rs.getDouble(field_paymentamount));
		entity.setBankID(rs.getLong(field_bank_id));
		entity.setTrtype(rs.getInt(field_tr_type));
		entity.setRemark(rs.getString(field_remark));
		return entity;
	}
	
	protected CashPayment readRecords(ResultSet rs, R_MODE mode) throws SQLException {
		CashPayment entity = new CashPayment();
		entity.setSlipID(rs.getLong(field_slip_id));
		entity.setSlipno(rs.getString(field_slip_no));
		entity.setCreatedDate(rs.getDate(field_created_date));
		entity.setModifiedDate(rs.getDate(field_modified_date));
		entity.setTrdate(rs.getDate(field_tr_date));
		entity.setCreatedBy(rs.getLong(field_created_by));
		entity.setModifiedBy(rs.getLong(field_modified_by));
		entity.setAgentID(rs.getLong(field_agent_id));
		entity.setDefaultbranchID(rs.getLong(field_defaultbranch_id));
		entity.setCurrencyID(rs.getLong(field_currency_id));
		entity.setPaymentamt(rs.getDouble(field_paymentamount));
		entity.setBankID(rs.getLong(field_bank_id));
		entity.setTrtype(rs.getInt(field_tr_type));
		entity.setRemark(rs.getString(field_remark));
		entity.setAgentcode(rs.getString(field_agent_code));
		entity.setBankcode(rs.getString(field_bank_code));
		
		if(mode == R_MODE.DETAIL) {
			ArrayList<CashPaymentDetails> l_list = new ArrayList<CashPaymentDetails>();
			
			CashPaymentDetails l_info = new CashPaymentDetails();
			l_info.setSlipID(rs.getLong("SlipID"));
			l_info.setSlipdetailID(rs.getLong("SlipDetailID"));
			l_info.setInvoiceNo(rs.getString("InvoiceNo"));
			l_info.setReceiptNo(rs.getString("ReceiptNo"));
			l_info.setSender(rs.getString("Sender"));
			l_info.setReceiver(rs.getString("Receiver"));
			l_info.setAddress(rs.getString("Address"));
			l_info.setCurrencyID(rs.getLong("D_CurrencyID"));
			l_info.setCurrencycode(rs.getString("CurrencyCode"));
			l_info.setRate(rs.getDouble("Rate"));
			l_info.setApplyamt(rs.getDouble("ApplyAmount"));
			l_info.setDptdetailid(rs.getLong("DefineDetailID"));
			l_info.setType_code(rs.getString("type_code"));
			l_info.setFrombranchid(rs.getLong("frombranchid"));
			
			l_list.add(l_info);
			
			entity.setInfodetaillist(l_list);
		}
		
		return entity;
	}

	protected void updateRecord(CashPayment entity, TRAN_MODE mode) throws SQLException {
		int i = 1;
		pstmt.setLong(i++, entity.getSlipID());
		pstmt.setString(i++, entity.getSlipno());
		if(mode == TRAN_MODE.UPDATE) {
			pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
			pstmt.setTimestamp(i++, new Timestamp(entity.getTrdate().getTime()));
			if(entity.getModifiedBy()!=null)
				pstmt.setLong(i++, entity.getModifiedBy());
			else
				pstmt.setString(i++, null);
		}
		else{
			pstmt.setTimestamp(i++, new Timestamp(entity.getCreatedDate().getTime()));
			pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
			pstmt.setTimestamp(i++, new Timestamp(entity.getTrdate().getTime()));
			if(entity.getCreatedBy()!=null)
				pstmt.setLong(i++, entity.getCreatedBy());
			else
				pstmt.setString(i++, null);
			if(entity.getModifiedBy()!=null)
				pstmt.setLong(i++, entity.getModifiedBy());
			else
				pstmt.setString(i++, null);
		}		
		if(entity.getAgentID()!=null) {
			if(entity.getAgentID()!=0) {
				pstmt.setLong(i++, entity.getAgentID());
			} else
				pstmt.setString(i++, null);
		} else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getDefaultbranchID());
		pstmt.setLong(i++, entity.getCurrencyID());
		pstmt.setDouble(i++, entity.getPaymentamt());
		if(entity.getBankID()!=null)
			pstmt.setLong(i++, entity.getBankID());
		else
			pstmt.setString(i++, null);
		pstmt.setInt(i++, entity.getTrtype());
		pstmt.setString(i++, entity.getRemark());
	}

	@Override
	public CashPayment findByCode(String code, long branchId)
			throws SQLException {
		// TODO Auto-generated method stub
		CashPayment l_info = null;
		query = "select  *  from CashPayment cp  " + 
				" where cp.slipno  = '" + code + "' and cp.defaultbranchid = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_info = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_info;
	}	
	
	//report
	@Override
	public ResultSet prepareCashPaymentReport(CashPaymentRpt info)
			throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CashPaymentRpt ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashPaymentRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}	
	
	private static void CashPaymentRptParameter(CallableStatement l_pstmt,
			CashPaymentRpt  info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getFromdate().getTime())).toString());
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getTodate().getTime())).toString());
		l_pstmt.setString(i++, info.getFromSlipID());
		l_pstmt.setString(i++, info.getToSlipID());
		l_pstmt.setString(i++, info.getFromBranchID());
		l_pstmt.setString(i++, info.getToBranchID());
		l_pstmt.setString(i++, info.getFromAgentID());
		l_pstmt.setString(i++, info.getToAgentID());
		l_pstmt.setString(i++, info.getFromTypeID());
		l_pstmt.setString(i++, info.getToTypeID());
		l_pstmt.setString(i++, info.getFromUserID());
		l_pstmt.setString(i++, info.getToUserID());
		l_pstmt.setString(i++, info.getFromCurrencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());
		l_pstmt.setString(i++, info.getFromReceiptNo());
		l_pstmt.setString(i++, info.getToReceiptNo());
		l_pstmt.setString(i++, info.getFromBankCheque());
		l_pstmt.setString(i++, info.getToBankCheque());
		l_pstmt.setString(i++, info.getGroupby());
		l_pstmt.setLong(i++, info.getBranchid());	
	}

	@Override
	public CashPaymentDataList findCashPaymentDataList(CriteriaParams cri)
			throws SQLException {
		// TODO Auto-generated method stub
		CashPaymentDataList l_DataList = new CashPaymentDataList();
		ArrayList<CashPayment> l_entityList = new ArrayList<CashPayment>();
		String l_cri = " and (slipno like N'%" + cri.getKeyword() + "%')"
				+ " and defaultbranchid =" + cri.getBranchId();
		query = "set rowcount "
				+ cri.getSize()
				+ " set rowcount 10 select * from (select DISTINCT row_number() over(order by slipid) as [rowno],c.* from" +
				" CashPayment c"		 
				+ " where 1 = 1 " + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();		
		String l_cri1 = " and (slipNo like N'%" + cri.getKeyword()
				+ "%')"  + "and defaultbranchid = " + cri.getBranchId();			
		query = "select count(slipid) from " + table_name + " where 1 = 1"
				+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	
	@Override
	public CashPayment findByCodeforRpt(String code, long branchId)
			throws SQLException {
		// TODO Auto-generated method stub
		CashPayment  l_info = null;
		query = "select  cd.ReceiptNo, c.TRDate from CashPayment c  " +
				" left join CashPaymentDetails cd on cd.SlipID = c.SlipID" + 
				" where  cd.ReceiptNo  = '" + code + "' and c.defaultbranchid = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_info = readRecordbyCode(rs);
		rs.close();
		pstmt.close();
		return l_info;
	}	
	
	protected CashPayment readRecordbyCode(ResultSet rs)
			throws SQLException {
		CashPayment l_entity = new CashPayment();		
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setTrdate(rs.getTimestamp("trdate"));		
		return l_entity;
	}
	
	@Override
	public CashPaymentDataList findCashPaymentforReceiptNo(CriteriaParams cri)
			throws SQLException {
		// TODO Auto-generated method stub
		CashPaymentDataList l_DataList = new CashPaymentDataList();
		ArrayList<CashPayment> l_entityList = new ArrayList<CashPayment>();		
		String l_cri = " and (ReceiptNo like N'%" + cri.getKeyword() + "%')"
				+ " and defaultbranchid =" + cri.getBranchId();
		query = "set rowcount "
				+ cri.getSize()
				+ " set rowcount 10 select * from (select DISTINCT row_number() over(order by c.slipid) as [rowno], cd.ReceiptNo, c.TRDate from" +
				" CashPayment c left join CashPaymentDetails cd on cd.SlipID = c.SlipID"		 
				+ " where 1 = 1 " + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecordbyCode(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();		
		String l_cri1 = " and (cd.ReceiptNo like N'%" + cri.getKeyword()
				+ "%')"  + "and defaultbranchid = " + cri.getBranchId();			
		query = "select count(cd.ReceiptNo) from " + table_name +
				" c  inner join CashPaymentDetails  cd on cd.SlipID = c.SlipID where 1 = 1"
				+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

}
